﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web;

namespace Dal
{
    class sqlhelper
    {
        private string M_connectionString  = System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;   //连接属性
  
         private SqlConnection sqlconnection;
        /// <summary>
         /// 构造函数初始化链接字符串，数据提供者并创建连接
        /// </summary>
          public sqlhelper()
         {
             try
              {
              sqlconnection = CreateConnection(M_connectionString);
              }
             catch (Exception ex)
             {
                 throw ex;
             }
          
             
         }
        /// <summary>
          /// 构造函数初始化链接字符串，数据提供者并创建连接
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
          private SqlConnection CreateConnection(string connectionString)
          {
              try
              {
                  SqlConnection Conn = new SqlConnection(connectionString);
                  return Conn;
              }
              catch (Exception ex)
              {
                  throw ex;
              }
          
          }
        /// <summary>
          /// 返回DataSet数据源
        /// </summary>
        /// <param name="SQltext"></param>
        /// <param name="dtName"></param>
        /// <returns></returns>
          public DataSet GetDataSet(string SQltext, string dtName)
          {

              try
              {
                  if (sqlconnection.State == ConnectionState.Closed)
                  {
                      sqlconnection.Open();
                  }
                  SqlCommand sqlCmd = new SqlCommand(SQltext, sqlconnection);
                  SqlDataAdapter adpter = new SqlDataAdapter();
                  adpter.SelectCommand = sqlCmd;
                  DataSet ds = new DataSet();
                  adpter.Fill(ds);
                  ds.Tables[0].TableName = dtName;
                  return ds;
              }
              catch (Exception ex)
              {
                  throw ex;
              }
              finally
              {
                  sqlconnection.Close();
              
              }
          }
        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
          public DataSet GetDataSet(SqlCommand sqlCmd)
          {

              try
              {
                  if (sqlconnection.State == ConnectionState.Closed)
                  {
                      sqlconnection.Open();
                  }                  
                  SqlDataAdapter adpter = new SqlDataAdapter();
                  adpter.SelectCommand = sqlCmd;
                  DataSet ds = new DataSet();
                  adpter.Fill(ds);
                  return ds;
              }
              catch (Exception ex)
              {
                  throw ex;
              }
              finally
              {
                  sqlconnection.Close();

              }
          }
        /// <summary>
        /// 添加参数值
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="sqltype"></param>
        /// <param name="value"></param>
          public void AddParameter(SqlCommand sqlcmd, string parameterName, DbType sqltype, Object value)
          {
              SqlParameter sqlparm = sqlcmd.CreateParameter();
              sqlparm.DbType = sqltype;
              sqlparm.Value = value;
              sqlparm.ParameterName = parameterName;
              sqlparm.Direction = ParameterDirection.Input;
              sqlcmd.Parameters.Add(sqlparm);
          
          }
         public SqlCommand  GetSqlCommand(string sqltext)
         {
               SqlCommand sqlcmd = sqlconnection.CreateCommand();
             try
             {
                
                 sqlcmd.CommandText = sqltext;
                 sqlcmd.CommandType = CommandType.Text;
                 return sqlcmd;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
             finally
             {
                 sqlcmd.Connection.Close();
             }
         }
         public SqlCommand GetSqlCommand(string prosqltext,CommandType ctype )
         {
             SqlCommand sqlcmd = sqlconnection.CreateCommand();
             try
             {

                 sqlcmd.CommandText = prosqltext;
                 sqlcmd.CommandType = ctype;
                 return sqlcmd;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
             finally
             {
                 sqlcmd.Connection.Close();
             }
         }
         public int ExecuteNonQuery(SqlCommand sqlcmd)
         {
             try
             {
                 if (sqlcmd.Connection.State == ConnectionState.Closed)
                 {
                     sqlcmd.Connection.Open();
                 }

                 int i = sqlcmd.ExecuteNonQuery();
                 return i;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
         
         }
         public object ExecuteScalar(SqlCommand sqlcmd)
         {
             try
             {
                 if (sqlcmd.Connection.State == ConnectionState.Closed)
                 {
                     sqlcmd.Connection.Open();
                 }

                 object i = sqlcmd.ExecuteScalar();
                 return i ;
             }
             catch (Exception ex)
             {
                 throw ex;
             }

         }


    }
}
